﻿CREATE FUNCTION [dbo].[fs_BuyBack_NS](
	@LoanID int = null,
	@StateID int = null,
	@Access int = 1
)RETURNS @IDs TABLE (LoanID int)
AS
BEGIN

IF @StateID IS NULL SELECT @StateID = StatusID FROM t_Loan WHERE ID = @LoanID

DECLARE @NSID int SET @NSID = dbo.UserNameSpaceID()

DECLARE @NSs TABLE(NameSpaceID int)
INSERT INTO @NSs SELECT NameSpaceID FROM t_NameSpaces WHERE ParentID = @NSID

DECLARE @StateIDs TABLE(StateID int)
INSERT INTO @StateIDs SELECT ID FROM fs_StateID(@StateID,@Access)

IF @LoanID IS NULL
	INSERT INTO @IDs
	SELECT l.ID FROM t_Loan l
	INNER JOIN t_BuyBack v ON l.ID = v.LoanID
	INNER JOIN @StateIDs S	ON v.StatusID = S.StateID
	INNER JOIN @NSs nss	ON l.NameSpaceID = nss.NameSpaceID
ELSE
	INSERT INTO @IDs
	SELECT l.ID FROM t_Loan l
	INNER JOIN t_BuyBack v ON l.ID = v.LoanID
	INNER JOIN @StateIDs S ON v.StatusID = S.StateID
	INNER JOIN @NSs nss	ON l.NameSpaceID = nss.NameSpaceID
	WHERE (l.ID = @LoanID)

RETURN

END






